﻿using System;
using System.Data;
using System.Text.RegularExpressions;
using System.Web.UI.WebControls;

namespace HiauPdm.Bll
{
    /// <summary>
    /// 基础数据
    /// </summary>
    public class BJcSj
    {
        /// <summary>
        /// 根据[上级物资类别ID]获得物资类别
        /// </summary>
        public static DataSet GetChildWzlb(int iWzLbId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.编号,A.类别名称,CAST((SELECT COUNT(1) FROM 物资类别 B,物资类别 C WHERE C.ISJID=B.ID AND B.ISJID=A.ID) AS BIT) 含子类别 FROM 物资类别 A WHERE ISNULL(A.ISJID,0)=@P0 ORDER BY A.编号", iWzLbId);
            }
        }

        /// <summary>
        /// 根据[上级物资类别ID]获得物资类别
        /// </summary>
        public static DataSet GetChildWzLs(int iWzLbId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.编号,A.类别名称,CAST((SELECT COUNT(1) FROM 物资类别 B WHERE B.ISJID=A.ID) AS BIT) 含子类别 FROM 物资类别 A WHERE ISNULL(A.ISJID,0)=@P0 ORDER BY A.编号", iWzLbId);
            }
        }

        /// <summary>
        /// 查询物资类别
        /// </summary>
        public static DataSet QueryWzlb(int iSjLbSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.编号,'['+B.类别名称+'] '+A.类别名称 类别名称 FROM 物资类别 A,物资类别 B WHERE A.ISJID=B.ID AND A.ISJID=@P0 ORDER BY A.编号", iSjLbSjId);
            }
        }

        /// <summary>
        /// 查询物资类别
        /// </summary>
        public static DataSet QueryWzlb(string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.ID,A.编号,'['+B.类别名称+'] '+A.类别名称 类别名称 FROM 物资类别 A,物资类别 B WHERE A.ISJID=B.ID AND NOT EXISTS(SELECT 1 FROM 物资类别 C WHERE C.ISJID=A.ID) AND {0} ORDER BY A.编号", BGlobal.BuildLike(sWhere, "A.类别名称", "B.类别名称")));
            }
        }

        /// <summary>
        /// 查询物资
        /// </summary>
        public static DataSet QueryWz(string sWhere, string sBh)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.ID,A.编号,A.名称,A.规格型号,A.单位,A.品牌厂家,B.库存数量,B.参考单价,C.编号 供货单位编号,C.名称 供货单位 FROM 物资目录V A LEFT JOIN 物资库存 B ON A.编号=B.物资编号 LEFT JOIN 供货单位 C ON B.最后供货=C.ID WHERE (@P0='' OR A.编号 LIKE @P0+'%') AND {0} ORDER BY A.编号", BGlobal.BuildLike(sWhere, "A.编号", "A.名称", "A.规格型号")), sBh);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资目录总记录数
        /// </summary>
        public static DataSet GetWzmlByCxCount(string sWhere, string sBh)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty && sBh == string.Empty)
                {
                    return da.ExecuteQuery("SELECT COUNT(1) FROM 物资目录");
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 物资目录V A WHERE {0} AND A.编号 LIKE @P0+'%'", BGlobal.BuildLike(sWhere, "A.编号", "A.名称", "A.规格型号")), sBh);
                }
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资目录
        /// </summary>
        public static DataSet GetWzmlByCx(string sWhere, string sBh, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty && sBh == string.Empty)
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.编号,A.名称,A.规格型号,A.单位,A.品牌厂家,A.编辑人 FROM 物资目录 A WHERE A.ID NOT IN(SELECT TOP {1} B.ID FROM 物资目录 B ORDER BY B.ID DESC) ORDER BY A.ID DESC", iPageSize, iPageSize * (iPageIndex - 1)));
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.编号,A.名称,A.规格型号,A.单位,A.品牌厂家,A.编辑人 FROM 物资目录V A WHERE {1} AND A.编号 LIKE @P0+'%' AND A.编号 NOT IN(SELECT TOP {2} B.编号 FROM 物资目录V B WHERE {3} AND B.编号 LIKE @P0+'%' ORDER BY B.编号) ORDER BY A.编号", iPageSize, BGlobal.BuildLike(sWhere, "A.编号", "A.名称", "A.规格型号"), iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "B.编号", "B.名称", "B.规格型号")), sBh);
                }
            }
        }

        /// <summary>
        /// 根据[物资目录ID]获得物资目录
        /// </summary>
        public static DataSet GetWzmlById(int iWzmlId, string sBh)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT ID,编号,类别,名称,规格型号,单位,品牌厂家,备注,编辑人,编辑时间 FROM 物资目录V WHERE ID=@P0 AND 编号=@P1", iWzmlId, sBh);
            }
        }

        /// <summary>
        /// 保存物资目录
        /// </summary>
        public static string SaveWzml(int iWzmlId, string sBh, string sLb, string sMc, string sGgxh, string sDw, string sPpcj, string sBz, string sBjr)
        {
            if (sBh == string.Empty || sMc.Trim() == string.Empty || sDw.Trim() == string.Empty)
            {
                return "物资编号、名称、计量单位不能为空！";
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (iWzmlId == 0)
                {
                    if (da.Execute("INSERT INTO 物资目录(编号,类别,名称,规格型号,单位,品牌厂家,备注,编辑人,编辑时间,状态) SELECT @P0+'-'+REPLACE(STR(ISNULL(MAX(RIGHT(编号,4)),0)+1,4),' ','0'),@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,0 FROM 物资目录 WHERE 编号 LIKE @P0+'%'", sBh, sLb, sMc.Trim(), sGgxh.Trim(), sDw.Trim(), sPpcj.Trim(), sBz.Trim(), sBjr, DateTime.Now) <= 0)
                    {
                        return "添加物资目录失败！";
                    }
                }
                else
                {
                    if (da.Execute("UPDATE 物资目录 SET 名称=@P1,规格型号=@P2,单位=@P3,品牌厂家=@P4,备注=@P5,编辑人=@P6,编辑时间=@P7 WHERE ID=@P0", iWzmlId, sMc.Trim(), sGgxh.Trim(), sDw.Trim(), sPpcj.Trim(), sBz.Trim(), sBjr, DateTime.Now) <= 0)
                    {
                        return "保存物资目录失败！";
                    }
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[物资目录ID]删除物资目录
        /// </summary>
        public static string DelWzml(int iWzmlId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("DELETE FROM 物资目录 WHERE ID=@P0 AND NOT EXISTS(SELECT 1 FROM 物资入库 WHERE 物资入库.物资编号=物资目录.编号)", iWzmlId) <= 0)
                {
                    return "删除物资目录失败，可能存在引用！";
                }
                return null;
            }
        }

        /// <summary>
        /// 获得供应商类别
        /// </summary>
        public static DataSet GetGysLb()
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT DISTINCT 类别 FROM 供货单位 ORDER BY 类别");
            }
        }

        /// <summary>
        /// 根据[查询条件]查询供应商
        /// </summary>
        public static DataSet QueryGys(string sWhere, string sLb)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.编号,A.名称,A.类别,A.地址 FROM 供货单位 A WHERE (@P0='' OR A.类别=@P0) AND {0} ORDER BY A.编号", BGlobal.BuildLike(sWhere, "A.编号", "A.名称")), sLb);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询供应商总记录数
        /// </summary>
        public static DataSet GetGysByCxCount(string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty)
                {
                    return da.ExecuteQuery("SELECT COUNT(1) FROM 供货单位");
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 供货单位 A WHERE {0}", BGlobal.BuildLike(sWhere, "A.编号", "A.名称", "A.主营产品", "A.法人代表", "A.联系人")));
                }
            }
        }

        /// <summary>
        /// 根据[查询条件]查询供应商
        /// </summary>
        public static DataSet GetGysByCx(string sWhere, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty)
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.编号,A.名称,A.类别,A.电话,A.传真,A.联系人,A.联系电话,A.编辑人 FROM 供货单位 A WHERE A.ID NOT IN(SELECT TOP {1} B.ID FROM 供货单位 B ORDER BY B.ID DESC) ORDER BY A.ID DESC", iPageSize, iPageSize * (iPageIndex - 1)));
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.编号,A.名称,A.类别,A.电话,A.传真,A.联系人,A.联系电话,A.编辑人 FROM 供货单位 A WHERE {1} AND A.ID NOT IN(SELECT TOP {2} B.ID FROM 供货单位 B WHERE {3} ORDER BY B.编号) ORDER BY A.编号", iPageSize, BGlobal.BuildLike(sWhere, "A.编号", "A.名称", "A.主营产品", "A.法人代表", "A.联系人"), iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "B.编号", "B.名称", "B.主营产品", "B.法人代表", "B.联系人")));
                }
            }
        }

        /// <summary>
        /// 根据[供应商ID]获得供应商
        /// </summary>
        public static DataSet GetGysById(int iGysId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT ID,编号,名称,类别,地址,邮编,电话,传真,主营产品,法人代表,税号,开户银行,银行账号,联系人,联系电话,联系邮件,联系QQ号,备注,编辑人,编辑时间 FROM 供货单位 WHERE ID=@P0", iGysId);
            }
        }

        /// <summary>
        /// 保存供应商
        /// </summary>
        public static string SaveGys(int iGysId, string sMc, string sLb, string sDz, string sYb, string sDh, string sCz, string sZycp, string sFrdb, string sSh, string sKhyh, string sYhzh, string sLxr, string sLxdh, string sLxyj, string sLxqqh, string sBz, string sBjr)
        {
            if (sMc.Trim() == string.Empty || sLb.Trim() == string.Empty || sLxr.Trim() == string.Empty || (sLxdh.Trim() == string.Empty && sDh.Trim() == string.Empty))
            {
                return "供应商名称、类别、联系人及电话不能为空！";
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (iGysId == 0)
                {
                    if (da.Execute("INSERT INTO 供货单位(编号,名称,类别,地址,邮编,电话,传真,主营产品,法人代表,税号,开户银行,银行账号,联系人,联系电话,联系邮件,联系QQ号,备注,状态,编辑人,编辑时间) SELECT 'GYS'+REPLACE(STR(ISNULL(MAX(RIGHT(编号,4)),0)+1,4),' ','0'),@P0,@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18 FROM 供货单位", sMc.Trim(), sLb.Trim(), sDz.Trim(), sYb.Trim(), sDh.Trim(), sCz.Trim(), sZycp.Trim(), sFrdb.Trim(), sSh.Trim(), sKhyh.Trim(), sYhzh.Trim(), sLxr.Trim(), sLxdh.Trim(), sLxyj.Trim(), sLxqqh.Trim(), sBz.Trim(), 9, sBjr, DateTime.Now) <= 0)
                    {
                        return "添加供应商失败，可能已存在！";
                    }
                }
                else
                {
                    if (da.Execute("UPDATE 供货单位 SET 名称=@P1,类别=@P2,地址=@P3,邮编=@P4,电话=@P5,传真=@P6,主营产品=@P7,法人代表=@P8,税号=@P9,开户银行=@P10,银行账号=@P11,联系人=@P12,联系电话=@P13,联系邮件=@P14,联系QQ号=@P15,备注=@P16,编辑人=@P17,编辑时间=@P18 WHERE ID=@P0", iGysId, sMc.Trim(), sLb.Trim(), sDz.Trim(), sYb.Trim(), sDh.Trim(), sCz.Trim(), sZycp.Trim(), sFrdb.Trim(), sSh.Trim(), sKhyh.Trim(), sYhzh.Trim(), sLxr.Trim(), sLxdh.Trim(), sLxyj.Trim(), sLxqqh.Trim(), sBz.Trim(), sBjr, DateTime.Now) <= 0)
                    {
                        return "保存供应商失败！";
                    }
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[供应商ID]删除供应商
        /// </summary>
        public static string DelGys(int iGysId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("DELETE FROM 供货单位 WHERE ID=@P0 AND NOT EXISTS(SELECT 1 FROM 物资入库 WHERE 物资入库.供货单位ID=供货单位.ID)", iGysId) <= 0)
                {
                    return "删除供应商失败，可能存在引用！";
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资用途
        /// </summary>
        public static DataSet QueryWzYt(string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.编号,A.物资用途,A.备注 FROM 物资用途 A WHERE {0} ORDER BY A.编号", BGlobal.BuildLike(sWhere, "A.编号", "A.物资用途")));
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资用途总记录数
        /// </summary>
        public static DataSet GetWzYtByCxCount(string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty)
                {
                    return da.ExecuteQuery("SELECT COUNT(1) FROM 物资用途");
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 物资用途 A WHERE {0}", BGlobal.BuildLike(sWhere, "A.编号", "A.物资用途")));
                }
            }
        }

        /// <summary>
        /// 根据[查询条件]查询物资用途
        /// </summary>
        public static DataSet GetWzYtByCx(string sWhere, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (sWhere == string.Empty)
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.编号,A.物资用途,A.备注 FROM 物资用途 A WHERE A.ID NOT IN(SELECT TOP {1} B.ID FROM 物资用途 B ORDER BY B.ID DESC) ORDER BY A.ID DESC", iPageSize, iPageSize * (iPageIndex - 1)));
                }
                else
                {
                    return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.编号,A.物资用途,A.备注 FROM 物资用途 A WHERE {1} AND A.ID NOT IN(SELECT TOP {2} B.ID FROM 物资用途 B WHERE {3} ORDER BY B.编号) ORDER BY A.编号", iPageSize, BGlobal.BuildLike(sWhere, "A.编号", "A.物资用途"), iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "B.编号", "B.物资用途")));
                }
            }
        }

        /// <summary>
        /// 根据[物资用途ID]获得物资用途
        /// </summary>
        public static DataSet GetWzYtById(int iWzYtId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT ID,编号,物资用途,备注 FROM 物资用途 WHERE ID=@P0", iWzYtId);
            }
        }

        /// <summary>
        /// 保存物资用途
        /// </summary>
        public static string SaveWzYt(int iWzYtId, string sWzYt, string sBz)
        {
            if (sWzYt.Trim() == string.Empty)
            {
                return "物资用途不能为空！";
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (iWzYtId == 0)
                {
                    if (da.Execute("INSERT INTO 物资用途(编号,物资用途,备注) SELECT 'CBDX'+REPLACE(STR(ISNULL(MAX(RIGHT(编号,4)),0)+1,4),' ','0'),@P0,@P1 FROM 物资用途", sWzYt.Trim(), sBz.Trim()) <= 0)
                    {
                        return "添加物资用途失败，可能已存在！";
                    }
                }
                else
                {
                    if (da.Execute("UPDATE 物资用途 SET 物资用途=@P1,备注=@P2 WHERE ID=@P0", iWzYtId, sWzYt.Trim(), sBz.Trim()) <= 0)
                    {
                        return "保存物资用途失败！";
                    }
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[物资用途ID]删除物资用途
        /// </summary>
        public static string DelWzYt(int iWzYtId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("DELETE FROM 物资用途 WHERE ID=@P0", iWzYtId) <= 0)
                {
                    return "删除物资用途失败！";
                }
                return null;
            }
        }

        /// <summary>
        /// 导入数据文件
        /// </summary>
        public static string ImportFile(FileUpload fUpload, ref string sOutFileName)
        {
            if (fUpload.PostedFile == null || fUpload.PostedFile.FileName == string.Empty)
            {
                return "导入数据文件失败！";
            }
            sOutFileName = string.Format("Ls{0}{1}.tmp", fUpload.Page.Session.SessionID, DateTime.Now.ToString("yyyyMMddHHmmssffffff"));
            try
            {
                fUpload.PostedFile.SaveAs(string.Format("{0}{1}", BGlobal.AttachmentPath, sOutFileName));
            }
            catch
            {
                return "导入数据文件失败！";
            }
            return null;
        }
    }
}
